package recycle.repository;

import io.vertx.core.CompositeFuture;
import io.vertx.core.Future;
import io.vertx.core.json.JsonObject;
import io.vertx.mysqlclient.MySQLPool;
import io.vertx.sqlclient.Row;
import io.vertx.sqlclient.RowSet;
import io.vertx.sqlclient.Tuple;
import recycle.pojo.User;

import java.util.ArrayList;
import java.util.List;

public class UserRepository {
    private final MySQLPool client;

    public UserRepository(MySQLPool client) {
        this.client = client;
    }

    private User rowToUser(Row row) {
        User user = new User();
        user.setId(row.getLong("id"));
        user.setUsername(row.getString("username"));
        user.setPassword(row.getString("password"));
        user.setNickname(row.getString("nickname"));
        user.setPhone(row.getString("phone"));
        user.setAddress(row.getString("address"));
        user.setRole(row.getInteger("role"));
        return user;
    }

    public Future<Long> insert(User user) {
        return client.preparedQuery("INSERT INTO user (phone, password) VALUES (?, ?) ")
                .execute(Tuple.of(user.getPhone(), user.getPassword()))
                .map(rows -> rows.property(io.vertx.mysqlclient.MySQLClient.LAST_INSERTED_ID));
    }

    public Future<JsonObject> selectList(User user, Integer pageNum, Integer pageSize) {
        // 参数校验和默认值设置
        if (pageNum == null || pageNum < 1) pageNum = 1;
        if (pageSize == null || pageSize < 1) pageSize = 10;

        // 计算偏移量
        int offset = (pageNum - 1) * pageSize;

        // 构建基础SQL
        StringBuilder sql = new StringBuilder("SELECT * FROM user WHERE 1=1");
        StringBuilder countSql = new StringBuilder("SELECT COUNT(*) AS total FROM user WHERE 1=1");

        // 使用List收集参数更灵活
        List<Object> queryParams = new ArrayList<>();
        List<Object> countParams = new ArrayList<>();

        // 添加条件
        if (user.getId() != null && user.getId() != 0) {
            sql.append(" AND id = ?");
            countSql.append(" AND id = ?");
            queryParams.add(user.getId());
            countParams.add(user.getId());
        }

        if (user.getUsername() != null && !user.getUsername().isEmpty()) {
            sql.append(" AND username LIKE CONCAT('%', ?, '%')");
            countSql.append(" AND username LIKE CONCAT('%', ?, '%')");
            queryParams.add(user.getUsername());
            countParams.add(user.getUsername());
        }

        if (user.getPhone() != null && !user.getPhone().isEmpty()) {
            sql.append(" AND phone LIKE CONCAT('%', ?, '%')");
            countSql.append(" AND phone LIKE CONCAT('%', ?, '%')");
            queryParams.add(user.getPhone());
            countParams.add(user.getPhone());
        }

        if (user.getRole() != null && user.getRole() != 0) {
            sql.append(" AND role = ?");
            countSql.append(" AND role = ?");
            queryParams.add(user.getRole());
            countParams.add(user.getRole());
        }

        // 为查询SQL添加分页参数
        sql.append(" LIMIT ? OFFSET ?");
        queryParams.add(pageSize);
        queryParams.add(offset);

        // 转换为Tuple
        Tuple queryTuple = Tuple.from(queryParams.toArray());
        Tuple countTuple = Tuple.from(countParams.toArray());

        // 执行查询和计数
        Future<RowSet<Row>> queryFuture = client.preparedQuery(sql.toString())
                .execute(queryTuple);

        Future<RowSet<Row>> countFuture = client.preparedQuery(countSql.toString())
                .execute(countTuple);

        // 合并结果
        return CompositeFuture.all(queryFuture, countFuture)
                .compose(composite -> {
                    RowSet<Row> rows = composite.resultAt(0);
                    RowSet<Row> countRows = composite.resultAt(1);

                    List<User> userList = new ArrayList<>();
                    for (Row row : rows) {
                        userList.add(rowToUser(row));
                    }

                    long total = countRows.iterator().next().getLong("total");
                    // int totalPages = (int) Math.ceil((double) total / pageSize);

                    // 构建返回结果
                    return Future.succeededFuture(new JsonObject()
                                    .put("records", userList)
                                    .put("total", total)
                            // .put("pageNum", pageNum)
                            // .put("pageSize", pageSize)
                            // .put("totalPages", totalPages)
                    );
                });
    }


    public Future<User> selectById(Long id) {
        return client.preparedQuery("SELECT * FROM user WHERE id = ?")
                .execute(Tuple.of(id))
                .map(rows -> {
                    if (rows.size() == 0) {
                        return null;
                    }
                    Row row = rows.iterator().next();
                    return rowToUser(row);
                });
    }

    // public Future<Void> updateById(User user) {
    //     return client.preparedQuery(" UPDATE user SET username = ?, password = ? WHERE id = ? ")
    //             .execute(Tuple.of(user.getUsername(), user.getPassword(), user.getId()))
    //             .mapEmpty();
    // }
    public Future<Void> updateById(User user) {
        // 1. 构建动态SQL和参数
        StringBuilder sqlBuilder = new StringBuilder("UPDATE user SET ");
        Tuple tuple = Tuple.tuple();
        List<Object> params = new ArrayList<>();

        // 2. 动态添加非空字段
        if (user.getUsername() != null && !user.getUsername().isEmpty()) {
            sqlBuilder.append("username = ?, ");
            params.add(user.getUsername());
        }

        if (user.getNickname() != null && !user.getNickname().isEmpty()) {
            sqlBuilder.append("nickname = ?, ");
            params.add(user.getNickname());
        }

        if (user.getPhone() != null && !user.getPhone().isEmpty()) {
            sqlBuilder.append("phone = ?, ");
            params.add(user.getPhone());
        }
        if (user.getAddress() != null && !user.getAddress().isEmpty()) {
            sqlBuilder.append("address = ?, ");
            params.add(user.getAddress());
        }

        if (user.getPassword() != null) {
            sqlBuilder.append("password = ?, ");
            params.add(user.getPassword());
        }

        // 3. 移除最后一个逗号
        if (!params.isEmpty()) {
            sqlBuilder.setLength(sqlBuilder.length() - 2); // 移除最后的", "
        } else {
            return Future.failedFuture("No fields to update");
        }

        // 4. 添加WHERE条件
        sqlBuilder.append(" WHERE id = ?");
        params.add(user.getId());

        // 5. 转换为Tuple
        tuple = Tuple.from(params);

        // 6. 执行更新
        return client.preparedQuery(sqlBuilder.toString())
                .execute(tuple)
                .mapEmpty();
    }

    public Future<Void> deleteById(Long id) {
        return client.preparedQuery("DELETE FROM user WHERE id = ?")
                .execute(Tuple.of(id))
                .mapEmpty();
    }

    public Future<User> selectByPhoneAndPassword(String phone, String password) {
        return client.preparedQuery("SELECT * FROM user WHERE phone = ? and password = ?")
                .execute(Tuple.of(phone, password))
                .map(rows -> {
                    if (rows.size() == 0) {
                        return null;
                    }
                    Row row = rows.iterator().next();
                    return rowToUser(row);
                });
    }


    public Future<User> selectByPhone(String phone) {
        return client.preparedQuery("SELECT * FROM user WHERE phone = ?")
                .execute(Tuple.of(phone))
                .map(rows -> {
                    if (rows.size() == 0) {
                        return null;
                    }
                    Row row = rows.iterator().next();
                    return rowToUser(row);
                });
    }
}